package com.yellowpage.omc.datacollect.service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.collections.map.LinkedMap;

import com.yellowpage.ndc.dao.ManageRegionDao;
import com.yellowpage.omc.datacollect.dao.DataCollectBatchDao;
import com.yellowpage.omc.datacollect.dao.DataCollectDao;
import com.yellowpage.omc.datacollect.data.DataCollect;
import com.yellowpage.omc.datacollect.data.DataCollectBatch;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;

public class DataCollectBatchServiceImpl extends BaseServiceImpl implements
		DataCollectBatchService {
	private DataCollectBatchDao dataCollectBatchDao;
	private ManageRegionDao manageRegionDao;
	private DataCollectDao dataCollectDao;

	public void setDataCollectBatchDao(DataCollectBatchDao dataCollectBatchDao) {
		this.dataCollectBatchDao = dataCollectBatchDao;
	}

	/* Smooth generate Method */
	public DataCollectBatch getDataCollectBatch(Long id) {
		return dataCollectBatchDao.get(id);
	}

	public List<DataCollectBatch> getAllDataCollectBatch() {
		return dataCollectBatchDao.getAll();
	}

	public List<Map> queryDataCollectBatchByPage(Map queryMap,
			PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.DATA_COLLECT_BATCH_ID , a.BATCH_NUM , a.DATA_COUNT , a.IMPORT_COUNT , a.CREATE_TIME , a.LAST_UPDATE_TIME , "
								+ "       (select display_name from CA_USER where user_id = a.CREATE_USER) as create_user, "
								+ "       (select NAME from OMC_DIC_TABLE where DIC_NAME = 'DATA_COLLECT_SOURCE' and VALUE = a.SOURCE) as SOURCE , "
								+ "       (select NAME from OMC_DIC_TABLE where DIC_NAME = 'DATA_BATCH_STATUS' and VALUE = a.STATUS) as STATUS , "
								+ "       a.FILE_NAME"
								+ "    FROM OMC_DATA_COLLECT_BATCH a WHERE 1=1 ")
				.addCondition(" and BATCH_NUM like :batchNum ")
				.addCondition(" and CREATE_USER = :createUser ")
				.addCondition(" and SOURCE = :source ")
				.addCondition(" and STATUS = :status ");

		sqlAssistant.setTail(" order by a.DATA_COLLECT_BATCH_ID desc ");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	public void initDataCollectStatus(DataCollect dataCollect) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL("select l.LIST_ID, l.TEL, c.CUST_ID, c.NAME1  as name, ADDR.ADDR"
						+ "  from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR, db_ndc.MANAGE_REGION mr "
						+ "  WHERE l.CUST_ID = c.CUST_ID"
						+ "    and l.ADDR_ID = ADDR.ADDR_ID"
						+ "    and ADDR.REGION_CODE =  mr.REGION_CODE "
						+ "    and l.TEL = '"
						+ dataCollect.getTel()
						+ "'"
						+ "    and mr.NAME = '"
						+ dataCollect.getCity()
						+ "'"
						+ "    and c.NAME1 = '"
						+ dataCollect.getName()
						+ "'"
						+ "    and ADDR.ADDR = '" + dataCollect.getAddr() + "'");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());

		if (resultList.size() > 0) {
			dataCollect.setStatus("MATCHED");
		} else {
			dataCollect.setStatus("PENDING");
		}

		// dataCollect.setStatus("DELETE");
	}

	public List processImportDataCollect(DataCollect dataCollect) {
		List dataCollectList = new ArrayList();

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL("select l.LIST_ID, l.TEL, c.CUST_ID, c.NAME1  as name, ADDR.ADDR "
						+ "  from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR "
						+ "  WHERE l.CUST_ID = c.CUST_ID"
						+ "    and l.ADDR_ID = ADDR.ADDR_ID"
						+ "    and ADDR.REGION_CODE =  '" + dataCollect.getRegionCode()	+ "'"
						+ "    and l.TEL = '" + dataCollect.getTel() + "'");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		
		BigDecimal listId;
		String custId = "";
		String custName = "";
		String listAddr = "";

		if (resultList.size() > 0) {
			for(int i = 0; i < resultList.size(); i++){
				
				DataCollect mDataCollect = new DataCollect();

				try{
				    BeanUtils.copyProperties(mDataCollect, dataCollect);
				}catch(Exception e){
					e.printStackTrace();
					log.error(e);
				}
				
				listId = (BigDecimal)resultList.get(i).get("LIST_ID");
				custName = (String)resultList.get(i).get("NAME");
				custId = (String)resultList.get(i).get("CUST_ID");
				listAddr = (String)resultList.get(i).get("ADDR");
				
				
				log.error("-------->  mDataCollect getName:" + mDataCollect.getName());
				log.error("-------->  mDataCollect getDataCollectBatchId:" + mDataCollect.getDataCollectBatchId());
				log.error("-------->  mDataCollect getAddr:" + mDataCollect.getAddr());
				
				log.error("-------->  dataCollect getDataCollectBatchId:" + mDataCollect.getDataCollectBatchId());
				
				mDataCollect.setListId(listId.longValue());
				
				if((custName != null && custName.equals(mDataCollect.getName()))
					&& (listAddr != null && listAddr.equals(mDataCollect.getAddr()))){
					mDataCollect.setStatus("MATCHED");
				}else{
					mDataCollect.setStatus("PENDING");
					mDataCollect.setOptType("UPDATE");
				}				

				dataCollectList.add(mDataCollect);				
			}			
			
		} else {
			dataCollect.setStatus("PENDING");
			dataCollect.setOptType("ADD");

			dataCollectList.add(dataCollect);
		}

		// dataCollect.setStatus("DELETE");

		return dataCollectList;
	}

	public void importDataCollect(List dataCollectList) {
		
		DataCollect dataCollect = null;

		try {
			
			for (int s = 0; s < dataCollectList.size(); s++) {
				dataCollect = (DataCollect) dataCollectList.get(s);
				
				String regionCode = manageRegionDao.getRegionCodeByRegionName(dataCollect.getCity());		
				
				if (regionCode == null) {
					regionCode = "000000";				}
				
				dataCollect.setRegionCode(regionCode);						
								
				
				List saveDataCollectList = new ArrayList();

				QueryAssistantSQL sqlAssistant = baseDao
						.newQueryAssistantSQL("select l.LIST_ID, l.TEL, c.CUST_ID, c.NAME1  as name, ADDR.ADDR "
								+ "  from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR "
								+ "  WHERE l.CUST_ID = c.CUST_ID"
								+ "    and l.ADDR_ID = ADDR.ADDR_ID"
								+ "    and ADDR.REGION_CODE =  '" + dataCollect.getRegionCode()	+ "'"
								+ "    and l.TEL = '" + dataCollect.getTel() + "'");

				List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
						new HashMap());
				
				BigDecimal listId;
				String custId = "";
				String custName = "";
				String listAddr = "";

				if (resultList.size() > 0) {
					for(int i = 0; i < resultList.size(); i++){
						
						DataCollect mDataCollect = new DataCollect();

						try{
						    BeanUtils.copyProperties(mDataCollect, dataCollect);
						}catch(Exception e){
							e.printStackTrace();
							log.error(e);
						}
						
						listId = (BigDecimal)resultList.get(i).get("LIST_ID");
						custName = (String)resultList.get(i).get("NAME");
						custId = (String)resultList.get(i).get("CUST_ID");
						listAddr = (String)resultList.get(i).get("ADDR");
						
						
						log.error("-------->  mDataCollect getName:" + mDataCollect.getName());
						log.error("-------->  mDataCollect getDataCollectBatchId:" + mDataCollect.getDataCollectBatchId());
						log.error("-------->  mDataCollect getAddr:" + mDataCollect.getAddr());
						
						log.error("-------->  dataCollect getDataCollectBatchId:" + mDataCollect.getDataCollectBatchId());
						
						mDataCollect.setListId(listId.longValue());
						
						if((custName != null && custName.equals(mDataCollect.getName()))
							&& (listAddr != null && listAddr.equals(mDataCollect.getAddr()))){
							mDataCollect.setStatus("MATCHED");
						}else{
							mDataCollect.setStatus("PENDING");
							mDataCollect.setOptType("UPDATE");
						}				

						dataCollectList.add(mDataCollect);				
					}			
					
				} else {
					dataCollect.setStatus("PENDING");
					dataCollect.setOptType("ADD");

					dataCollectList.add(dataCollect);
				}
				
				
				for(int i = 0; i < saveDataCollectList.size(); i++){
					dataCollectDao.save((DataCollect)saveDataCollectList.get(i));
				}				
				
			}

			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Map getDataSourceMap() {
		Map dataSourceMap = new LinkedMap();

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" select NAME, VALUE from OMC_DIC_TABLE where DIC_NAME = 'DATA_COLLECT_SOURCE' ");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());

		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("NAME"));
		}

		return dataSourceMap;
	}

	public Map getBatchStatusMap() {
		Map dataSourceMap = new LinkedMap();

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" select NAME, VALUE from OMC_DIC_TABLE where DIC_NAME = 'DATA_BATCH_STATUS' ");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());

		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("NAME"));
		}

		return dataSourceMap;
	}
	
	

	public void saveDataCollectBatch(DataCollectBatch dataCollectBatch) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL("select sn.seq_num as seq_num from OMC_SEQ_NUM sn where sn.seq_name = 'OMC_DATA_COLLECT_BATCH' ");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());

		Integer seqNum = (Integer) resultList.get(0).get("SEQ_NUM");
		
       String code = "";
		
		for(int i = 0; i < (6 - seqNum.toString().length()); i++ ){
			code = code + "0";
		}
		code = code + 	seqNum.toString();
		

		dataCollectBatch.setBatchNum(dataCollectBatch.getBatchNum() + "_" + code);

		dataCollectBatchDao.save(dataCollectBatch);

		try {

			baseDao.getConnection()
					.createStatement()
					.execute(
							"update  OMC_SEQ_NUM sn "
									+ "   set sn.seq_num = "
									+ (seqNum.intValue() + 1)
									+ "  where sn.seq_name = 'OMC_DATA_COLLECT_BATCH'");

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void updateDataCollectBatch(DataCollectBatch dataCollectBatch) {
		dataCollectBatchDao.update(dataCollectBatch);
	}

	public void delete(List deleteList) {
		dataCollectBatchDao.remove(deleteList);
	}

	public ManageRegionDao getManageRegionDao() {
		return manageRegionDao;
	}

	public void setManageRegionDao(ManageRegionDao manageRegionDao) {
		this.manageRegionDao = manageRegionDao;
	}

	public DataCollectDao getDataCollectDao() {
		return dataCollectDao;
	}

	public void setDataCollectDao(DataCollectDao dataCollectDao) {
		this.dataCollectDao = dataCollectDao;
	}

}